﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Text.RegularExpressions;

namespace Database
{
    /// <summary>
    /// Đối tượng xử lý cơ sở dữ liệu SQL Server.
    /// </summary>
    public class MSSqlConnection : ConnectionDB
    {
        #region Field
        private static MSSqlConnection cnn = null;
        private string _strConnect = "";
        private SqlConnection _connecttion = new SqlConnection();
        #endregion

        #region Property
        /// <summary>
        /// Lấy và gán chuỗi kết nối.
        /// </summary>
        /// <value>
        /// Chuỗi kết nối.
        /// </value>
        public string Text
        {
            private get { return _strConnect; }
            set { _strConnect = value; }
        }
        #endregion

        #region Đóng mở kết nối
        private void Open()
        {
            if (_connecttion.State == ConnectionState.Closed)
            {
                _connecttion.ConnectionString = _strConnect;
                _connecttion.Open();
            }
        }

        private void Close()
        {
            if (_connecttion.State == ConnectionState.Open || _connecttion.State == ConnectionState.Connecting)
            {
                _connecttion.Close();
            }
        }
        #endregion

        #region Hàm tạo đối tượng
        /// <summary>
        /// Tạo mới 1 đối tượng <see cref="MSSqlConnection" />.
        /// </summary>
        /// <returns></returns>
        public static MSSqlConnection Create()
        {
            if (cnn == null)
            {
                cnn = new MSSqlConnection();
                return cnn;
            }
            return cnn;
        }

        /// <summary>
        /// Tạo mới 1 đối tượng <see cref="MSSqlConnection" />.
        /// </summary>
        /// <param name="server">Tên server.</param>
        /// <param name="database">Tên database.</param>
        /// <returns></returns>
        public static MSSqlConnection Create(string server, string database)
        {
            if (cnn == null)
            {
                cnn = new MSSqlConnection(server, database);
                return cnn;
            }
            return cnn;
        }

        /// <summary>
        /// Tạo mới 1 đối tượng <see cref="MSSqlConnection" />.
        /// </summary>
        /// <param name="server">Tên server.</param>
        /// <param name="database">Tên database.</param>
        /// <param name="user">Tên đăng nhập.</param>
        /// <param name="password">Mật khẩu.</param>
        /// <returns></returns>
        public static MSSqlConnection Create(string server, string database, string user, string password)
        {
            if (cnn == null)
            {
                cnn = new MSSqlConnection(server, database, user, password);
                return cnn;
            }
            return cnn;
        }

        /// <summary>
        /// Tạo mới 1 đối tượng <see cref="MSSqlConnection" />.
        /// </summary>
        /// <param name="fullPath">Đường dẫn đầy đủ.</param>
        /// <returns></returns>
        public static MSSqlConnection Create(string fullPath)
        {
            if (cnn == null)
            {
                cnn = new MSSqlConnection(fullPath);
                return cnn;
            }
            return cnn;
        }
        #endregion

        #region Khởi tạo và hủy
        /// <summary>
        /// Tạo mới 1 đối tượng <see cref="MSSqlConnection" />.
        /// </summary>
        private MSSqlConnection()
        {
            _strConnect = "";
        }

        /// <summary>
        /// Tạo mới 1 đối tượng <see cref="MSSqlConnection" />.
        /// </summary>
        /// <param name="server">Tên server.</param>
        /// <param name="database">Tên database.</param>
        private MSSqlConnection(string server, string database)
        {
            _strConnect = "server=" + server
                + ";database=" + database
                + ";Integrated Security=SSPI";
        }

        /// <summary>
        /// Tạo mới 1 đối tượng <see cref="MSSqlConnection" />.
        /// </summary>
        /// <param name="server">Tên server.</param>
        /// <param name="database">Tên database.</param>
        /// <param name="user">Tên đăng nhập.</param>
        /// <param name="password">Mật khẩu.</param>
        private MSSqlConnection(string server, string database, string user, string password)
        {
            _strConnect = "server=" + server
                + ";database=" + database
                + ";Integrated Security=false;"
                + "uid=" + user
                + ";pwd=" + password;
        }

        /// <summary>
        /// Tạo mới 1 đối tượng <see cref="MSSqlConnection" />.
        /// </summary>
        /// <param name="fullPath">Đường dẫn đầy đủ.</param>
        private MSSqlConnection(string fullPath)
        {
            //_strConnect = @"Data Source=.\SQLEXPRESS;"
            _strConnect = @"Server=localhost"
                + "AttachDbFilename=" + fullPath + ";"
                + "Integrated Security=true;"
                + "User Instance=true";
        }

        /// <summary>
        /// Tạo chuỗi kết nối cho đối tượng
        /// </summary>
        /// <param name="server">Tên server.</param>
        /// <param name="database">Tên database.</param>
        public void ConnectionText(string server, string database)
        {
            _strConnect = "server=" + server
                + ";database=" + database
                + ";Integrated Security=SSPI";
        }

        /// <summary>
        /// Tạo chuỗi kết nối cho đối tượng
        /// </summary>
        /// <param name="server">Tên server.</param>
        /// <param name="database">Tên database.</param>
        /// <param name="user">Tên đăng nhập.</param>
        /// <param name="password">Mật khẩu.</param>
        public void ConnectionText(string server, string database, string user, string password)
        {
            _strConnect = "server=" + server
                + ";database=" + database
                + ";Integrated Security=false;"
                + "uid=" + user
                + ";pwd=" + password;
        }

        /// <summary>
        /// Tạo chuỗi kết nối cho đối tượng
        /// </summary>
        /// <param name="fullPath">Đường dẫn đầy đủ.</param>
        public void ConnectionText(string fullPath)
        {
            //_strConnect = @"Data Source=.\SQLEXPRESS;"
            _strConnect = @"Server=localhost"
                + "AttachDbFilename=" + fullPath + ";"
                + "Integrated Security=true;"
                + "User Instance=true";
        }
        #endregion

        #region Kiểm tra kết nối
        /// <summary>
        /// Kiểm tra kết nối.
        /// </summary>
        /// <returns></returns>
        public override bool Check()
        {
            try
            {
                Open();
            }
            catch /*(Exception ex)*/
            {
                return false;
            }
            Close();
            return true;
        }
        #endregion

        #region Thực hiện lệnh thực thi SQL
        /// <summary>
        /// Thực thi lệnh sql
        /// </summary>
        /// <param name="execute">Chuỗi thực thi.</param>
        /// <returns>Số trường được thực thi</returns>
        public override int Execute(string execute)
        {
            int number;
            if (Check())
            {
                Open();
                SqlCommand _command = new SqlCommand();
                _command.Connection = _connecttion;
                _command.CommandText = execute;
                try
                {
                    number = _command.ExecuteNonQuery();
                }
                catch /*(Exception ex)*/
                {
                    Close();
                    return -1;
                }
                Close();
                return number;
            }
            else
            {
                return -2;
            }
        }

        /// <summary>
        /// Thực thi lệnh sql
        /// </summary>
        /// <param name="execute">
        /// Chuỗi thực thi với tham số là số nguyên dương nằm trong dấu {}.
        /// VD: Delete Table where id like {0};
        /// </param>
        /// <param name="obj">Các tham số.</param>
        /// <returns></returns>
        public override int Execute(string execute, params object[] obj)
        {
            #region Xử lý chuỗi execute
            string pattern = "{\\d+}";
            string result = null;
            int numberValue = 0;
            Regex Reg = new Regex(pattern);
            MatchCollection mc = Reg.Matches(execute);
            if (mc.Count > 0)
            {
                numberValue = mc.Count;
                string[] temp = Reg.Split(execute);
                for (int i = 0; i < temp.Length - 1; i++)
                {
                    result += temp[i] + "@value" + mc[i].Value.Substring(1, mc[i].Value.IndexOf('}') - 1);
                }
                result += temp[temp.Length - 1];
            }
            #endregion
            int number;
            if (Check())
            {
                Open();
                SqlCommand _command = new SqlCommand();
                _command.Connection = _connecttion;
                if (string.IsNullOrEmpty(result))
                {
                    _command.CommandText = execute;
                }
                else
                {
                    _command.CommandText = result;
                    for (int i = 0; i < numberValue; i++)
                    {
                        _command.Parameters.AddWithValue("@value" + i.ToString(), obj[i]);
                    }
                }
                try
                {
                    number = _command.ExecuteNonQuery();
                }
                catch /*(Exception ex)*/
                {
                    Close();
                    return -1;
                }
                Close();
                return number;
            }
            else
            {
                return -2;
            }
        }

        /// <summary>
        /// Thực thi StoredProcedure
        /// </summary>
        /// <param name="ProcName">Tên của Procedure.</param>
        /// <param name="obj">Danh sách các tham só.</param>
        /// <returns></returns>
        public override int ExecuteProcedure(string ProcName, params object[] obj)
        {
            SqlCommand command = new SqlCommand();
            command.Connection = _connecttion;
            string strProc = "execute " + ProcName;
            for (int i = 0; i < obj.Length - 1; i++)
            {
                strProc += " @value" + i.ToString() + ",";
                command.Parameters.AddWithValue("@value" + i.ToString(), obj[i]);
            }
            command.CommandText = strProc + " @value" + (obj.Length - 1).ToString() + ";";
            command.Parameters.AddWithValue("@value" + (obj.Length - 1).ToString(), obj[obj.Length - 1]);
            if (Check())
            {
                int count;
                Open();
                try
                {
                    count = command.ExecuteNonQuery();
                }
                catch
                {
                    Close();
                    return -1;
                }
                Close();
                return count;
            }
            return -2;
        }
        #endregion

        #region Thực hiện lệnh Query
        /// <summary>
        /// Thực thi câu lệnh truy vấn
        /// </summary>
        /// <param name="select">Chuỗi truy vấn.</param>
        /// <returns></returns>
        public override object[][] Querys(string select)
        {
            object[][] obj;
            if (Check())
            {
                DataSet _dataset = new DataSet();
                SqlCommand _command = new SqlCommand(select, _connecttion);
                SqlDataAdapter _dataAdapter = new SqlDataAdapter(_command);
                try
                {
                    _dataAdapter.Fill(_dataset, "Data_DataSet");
                    DataTable _datatable = _dataset.Tables["Data_DataSet"];
                    obj = new object[_datatable.Rows.Count][];
                    for (int i = 0; i < _datatable.Rows.Count; i++)
                    {
                        obj[i] = new object[_datatable.Columns.Count];
                        obj[i] = _datatable.Rows[i].ItemArray;
                    }
                }
                catch /*(Exception ex)*/
                {
                    return null;
                }
                return obj;
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// Thực thi câu lệnh truy vấn
        /// </summary>
        /// <param name="select">Chuỗi truy vấn.</param>
        /// <param name="obj">Các tham só.</param>
        /// <returns></returns>
        public override object[][] Querys(string select, params object[] obj)
        {
            #region Xử lý chuỗi select
            string pattern = "{\\d+}";
            string result = null;
            int numberValue = 0;
            Regex Reg = new Regex(pattern);
            MatchCollection mc = Reg.Matches(select);
            if (mc.Count > 0)
            {
                numberValue = mc.Count;
                string[] temp = Reg.Split(select);
                for (int i = 0; i < temp.Length - 1; i++)
                {
                    result += temp[i] + "@value" + mc[i].Value.Substring(1, mc[i].Value.IndexOf('}') - 1);
                }
                result += temp[temp.Length - 1];
            }
            #endregion
            object[][] objtemp;
            if (Check())
            {
                DataSet _dataset = new DataSet();
                SqlCommand _command = new SqlCommand();
                _command.Connection = _connecttion;
                if (string.IsNullOrEmpty(result))
                {
                    _command.CommandText = select;
                }
                else
                {
                    _command.CommandText = result;
                    for (int i = 0; i < numberValue; i++)
                    {
                        _command.Parameters.AddWithValue("@value" + i.ToString(), obj[i]);
                    }
                }
                SqlDataAdapter _dataAdapter = new SqlDataAdapter(_command);
                try
                {
                    _dataAdapter.Fill(_dataset, "Data_DataSet");
                    DataTable _datatable = _dataset.Tables["Data_DataSet"];
                    objtemp = new object[_datatable.Rows.Count][];
                    for (int i = 0; i < _datatable.Rows.Count; i++)
                    {
                        objtemp[i] = new object[_datatable.Columns.Count];
                        objtemp[i] = _datatable.Rows[i].ItemArray;
                    }
                }
                catch /*(Exception ex)*/
                {
                    return null;
                }
                return objtemp;
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// Thực thi câu lệnh truy vấn
        /// </summary>
        /// <param name="select">Chuỗi truy vấn.</param>
        /// <returns></returns>
        public override DataTable Query(string select)
        {
            DataTable _datatable = new DataTable();
            if (Check())
            {
                DataSet _dataset = new DataSet();
                SqlCommand _command = new SqlCommand(select, _connecttion);
                SqlDataAdapter _dataAdapter = new SqlDataAdapter(_command);
                try
                {
                    _dataAdapter.Fill(_dataset, "Data_DataSet");
                    _datatable = _dataset.Tables["Data_DataSet"];
                }
                catch /*(Exception ex)*/
                {
                    return null;
                }
            }
            return _datatable;
        }

        /// <summary>
        /// Thực thi câu lệnh truy vấn
        /// </summary>
        /// <param name="select">Chuỗi truy vấn.</param>
        /// <param name="obj">Các tham só.</param>
        /// <returns></returns>
        public override DataTable Query(string select, params object[] obj)
        {
            #region Xử lý chuỗi select
            string pattern = "{\\d+}";
            string result = null;
            int numberValue = 0;
            Regex Reg = new Regex(pattern);
            MatchCollection mc = Reg.Matches(select);
            if (mc.Count > 0)
            {
                numberValue = mc.Count;
                string[] temp = Reg.Split(select);
                for (int i = 0; i < temp.Length - 1; i++)
                {
                    result += temp[i] + "@value" + mc[i].Value.Substring(1, mc[i].Value.IndexOf('}') - 1);
                }
                result += temp[temp.Length - 1];
            }
            #endregion
            DataTable _datatable = new DataTable();
            if (Check())
            {
                DataSet _dataset = new DataSet();
                SqlCommand _command = new SqlCommand();
                _command.Connection = _connecttion;
                if (string.IsNullOrEmpty(result))
                {
                    _command.CommandText = select;
                }
                else
                {
                    _command.CommandText = result;
                    for (int i = 0; i < numberValue; i++)
                    {
                        _command.Parameters.AddWithValue("@value" + i.ToString(), obj[i]);
                    }
                }
                SqlDataAdapter _dataAdapter = new SqlDataAdapter(_command);
                try
                {
                    _dataAdapter.Fill(_dataset, "Data_DataSet");
                    _datatable = _dataset.Tables["Data_DataSet"];
                }
                catch /*(Exception ex)*/
                {
                    return null;
                }
            }
            else
            {
                return null;
            }
            return _datatable;
        }

        /// <summary>
        /// Thực thi truy vấn bằng StoredProcedure
        /// </summary>
        /// <param name="ProcName">Tên của Procedure.</param>
        /// <returns></returns>
        public override DataTable QueryProcedure(string ProcName)
        {
            DataTable _datatable = null;
            SqlCommand _command = new SqlCommand();
            _command.Connection = _connecttion;
            _command.CommandType = CommandType.StoredProcedure;
            _command.CommandText = ProcName;
            if (Check())
            {
                Open();
                try
                {
                    _datatable = new DataTable();
                    _datatable.Load(_command.ExecuteReader());
                }
                catch
                {
                    Close();
                    return null;
                }
                Close();
            }
            return _datatable;
        }

        /// <summary>
        /// Thực thi truy vấn bằng StoredProcedure
        /// </summary>
        /// <param name="ProcName">Tên của Procedure.</param>
        /// <param name="obj">Danh sách các tham số.</param>
        /// <returns></returns>
        public override DataTable QueryProcedure(string ProcName, params object[] obj)
        {
            DataTable datatable = null;
            SqlCommand command = new SqlCommand();
            command.Connection = _connecttion;
            if (Check())
            {
                Open();
                string strProc = "execute " + ProcName;
                for (int i = 0; i < obj.Length - 1; i++)
                {
                    strProc += " @value" + i.ToString() + ",";
                    command.Parameters.AddWithValue("@value" + i.ToString(), obj[i]);
                }
                command.CommandText = strProc + " @value" + (obj.Length - 1).ToString() + ";";
                command.Parameters.AddWithValue("@value" + (obj.Length - 1).ToString(), obj[obj.Length - 1]);
                try
                {
                    datatable = new DataTable();
                    datatable.Load(command.ExecuteReader());
                    command.Clone();
                }
                catch
                {
                    Close();
                    return null;
                }
                finally
                {
                    command.Dispose();
                    command = null;
                    Close();
                }
            }
            return datatable;
        }
        #endregion
    }
}
